﻿namespace TrackMoney
{
    public class sql
    {
        public enum SQL
        {
            S_CONNECTION_STR,
            /// <summary>
            /// category
            /// </summary>
            S_ISSUE_CATEGORY,
            S_GET_CATEGORY,
            S_DEL_CATEGORY,
            /// <summary>
            /// income
            /// </summary>
            S_ISSUE_INCOME,
            S_GET_INCOME,
            S_GET_INCOME_TOTAL,

            S_GET_EXPENSE,
            S_ISSUE_EXPENSE,

            S_GET_SUMMARY_INCOME,
            S_GET_SUMMARY_EXPENSE,
            /// <summary>
            /// Login
            /// </summary>
            S_SAVE_SIGN_UP,
            S_FETCH_ID,
            S_FETCH_PASSWROD,
            S_UPDATE_PASSWORD
        }


        public static string[] SQL_ARRAY =
        {
            "User ID=faceozOperator;Password=faceozOperator; Persist Security Info=True;Initial Catalog=TrackMoney;Server=localhost;",
            "INSERT INTO fzCategories(caType, caName, caIndex, caOwner) VALUES ('{0}', N'{1}', 0, {2})",
            "SELECT TOP {0} caKey, caName, caType FROM fzCategories where {1} order by caIndex DESC",
            //Centre controll all delete actions
            "DELETE FROM {0} WHERE 1=1 and ( {1} )",
            "INSERT INTO fzIncomes(inCategory, inAmount, inDate, inNotes, inOwner, inGroup) VALUES ('{0}', '{1}', '{2}', N'{3}', {5}, '{4}')",
            "SELECT TOP {0} a.*, b.caName FROM fzIncomes a, fzCategories b WHERE b.caKey = a.inCategory {2} and a.inKey not in (select top {1} inKey from fzIncomes order by inDate DESC ) and {3} order by inDate DESC",
            "SELECT count(*) from {0} where {3}Date>='{1}' and {3}Date <='{2}' and {3}Owner={4}",
            "SELECT TOP {0} a.*, b.caName FROM fzExpenses a, fzCategories b WHERE b.caKey = a.epCategory {2} and a.epKey not in (select top {1} epKey from fzExpenses order by epDate DESC ) and {3} order by epDate DESC",
            "INSERT INTO fzExpenses(epCategory, epAmount, epDate, epNotes, epOwner, epGroup) VALUES ('{0}', '{1}', '{2}', N'{3}', {5}, '{4}')",
            "select sum(inAmount) from fzIncomes where inDate>='{0}' and inDate <='{1}' and inOwner={2}",
            "select sum(epAmount) from fzExpenses where epDate>='{0}' and epDate <='{1}' and epOwner={2}",
            //login
            "INSERT INTO fzAccounts(acName, acEmail, acPassword, acAge, acGender) Values ('{0}', '{1}', '{2}', {3}, {4})",
            "SELECT acKey from fzAccounts WHERE acEmail='{0}'",
            "SELECT acPassword from fzAccounts WHERE acEmail='{0}'",
            "UPDATE fzAccounts set acPassword='{1}' WHERE acEmail='{0}' "
        };

        /// <summary>
        /// Get the GUI
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        public static string GetSQL(SQL e)
        {
            return SQL_ARRAY[(int)e];
        }
    }
}